﻿using System;
using System.Collections.Generic;
using System.Data;
using IceCS.GoodsSale.Models.GoodsModels;
using Microsoft.Data.SqlClient;

namespace IceCS.GoodsSale.DAL
{
    public class GoodsAccesser
    {
        DBHelper dh = new DBHelper();

        //添加商品信息的方法
        public bool InsertGoods(Goods goods)
        {
            string sqlStr = "insert into TB_Goods values(@GoodsID,@Name,@InPrice,@OutPrice,@Inventory)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@GoodsID",goods.GoodsID),
                new SqlParameter("@Name",goods.Name),
                new SqlParameter("@InPrice",goods.InPrice),
                new SqlParameter("@OutPrice",goods.OutPrice),
                new SqlParameter("@Inventory",goods.Inventory),
            };
            return dh.ExcuteCommand(sqlStr, param);
        }

        //删除商品信息的方法
        public bool DeleteGoods(string goodsID)
        {
            string sqlStr = "delete from TB_Goods where GoodsID='" + goodsID + "'";
            return dh.ExcuteCommand(sqlStr);
        }

        //修改商品信息的方法
        public bool UpdateGoods(Goods goods)
        {
            string sqlStr = "update TB_Goods set Name=@Name,InPrice=@InPrice,OutPrice=@OutPrice,Inventory=@Inventory where GoodsID=@GoodsID";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@GoodsID",goods.GoodsID),
                new SqlParameter("@Name",goods.Name),
                new SqlParameter("@InPrice",goods.InPrice),
                new SqlParameter("@OutPrice",goods.OutPrice),
                new SqlParameter("@Inventory",goods.Inventory),
            };
            return dh.ExcuteCommand(sqlStr, param);
        }

        //查询指定商品信息的方法
        public Goods SelectGoods(string goodsID)
        {
            string sqlStr = "select Name,InPrice,OutPrice,Inventory from TB_Goods where GoodsID='" + goodsID + "'";
            DataTable dt = dh.GetDataTable(sqlStr);
            if (dt.Rows.Count != 0)
            {
                Goods goods = new Goods();
                goods.GoodsID = goodsID;
                goods.Name = dt.Rows[0].ItemArray[0].ToString();
                goods.InPrice = decimal.Parse(dt.Rows[0].ItemArray[1].ToString());
                goods.OutPrice = decimal.Parse(dt.Rows[0].ItemArray[2].ToString());
                goods.Inventory = int.Parse(dt.Rows[0].ItemArray[3].ToString());
                return goods;
            }
            else
            {
                return null;
            }
        }

        //查询商品信息的方法，返回一个商品信息列表
        public List<Goods> SelectGoodss()
        {
            string sqlStr = "select * from TB_Goods";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Goods> list = new List<Goods>();
            foreach (DataRow r in dt.Rows)
            {
                Goods goods = new Goods();
                goods.GoodsID = r["GoodsID"].ToString();
                goods.Name = r["Name"].ToString();
                goods.InPrice = decimal.Parse(r["InPrice"].ToString());
                goods.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                goods.Inventory = int.Parse(r["Inventory"].ToString());
                list.Add(goods);
            }
            return list;
        }

        //按指定条件查询商品信息的方法，返回一个商品信息列表
        public List<Goods> SelectGoodss(string selectCondition, string selectValue)
        {
            string sqlStr = "select * from TB_Goods where " + selectCondition + " like '%" + selectValue + "%'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Goods> list = new List<Goods>();
            foreach (DataRow r in dt.Rows)
            {
                Goods goods = new Goods();
                goods.GoodsID = r["GoodsID"].ToString();
                goods.Name = r["Name"].ToString();
                goods.InPrice = decimal.Parse(r["InPrice"].ToString());
                goods.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                goods.Inventory = int.Parse(r["Inventory"].ToString());
                list.Add(goods);
            }
            return list;
        }

        //查询库存小于指定数字的商品信息的方法，返回一个商品信息列表
        public List<Goods> SelectAlarmGoodss(int alarmNumber)
        {
            string sqlStr = "select * from TB_Goods where Inventory<'" + alarmNumber + "'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Goods> list = new List<Goods>();
            foreach (DataRow r in dt.Rows)
            {
                Goods goods = new Goods();
                goods.GoodsID = r["GoodsID"].ToString();
                goods.Name = r["Name"].ToString();
                goods.InPrice = decimal.Parse(r["InPrice"].ToString());
                goods.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                goods.Inventory = int.Parse(r["Inventory"].ToString());
                list.Add(goods);
            }
            return list;
        }
    }
}
